﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace StockFinder.DataAccess
{
    public class GeneralDataAccess : DataAccessBase
    {
        private const string TRUNCATE_ALL_PROCEDURE = "dbo.TruncateAll";

        public void TruncateAll()
        {
            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand(TRUNCATE_ALL_PROCEDURE, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.ExecuteNonQuery();
            }
        }

        public void RemovePriceIndex()
        {
            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand("IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DailyPrice]') AND name = N'IDX_Price_SymbolId') DROP INDEX dbo.DailyPrice.IDX_Price_SymbolId", connection) { CommandType = System.Data.CommandType.Text })
            {
                command.ExecuteNonQuery();
            }
        }

        public void AddPriceIndex()
        {
            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand("IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DailyPrice]') AND name = N'IDX_Price_SymbolId') CREATE NONCLUSTERED INDEX [IDX_Price_SymbolId] ON [dbo].[DailyPrice] ( [SymbolId] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY] GO", connection) { CommandType = System.Data.CommandType.Text })
            {
                command.ExecuteNonQuery();
            }
        }
    }
}
